DB2数字类型转换成字符串类型,例:ORACLE与DB2 | 您所在的位置:网站首页 › db2 字符串类型 › DB2数字类型转换成字符串类型,例:ORACLE与DB2 |
select 1/3 from sysibm.sysdummy1;
如果想要最初的语句1/3得到非零值。可以使用如下方法: (1) select 1.0/3 from sysibm.sysdummy1; ---得到小数值 结果: --0.333333333333333333333333333333 (2) select 1/3.0 from sysibm.sysdummy1; ----同样得到小数值 结果: --0.3333333333333333333 (3) select cast(1 as float)/3 from sysibm.sysdummy1; --使用cast将1转为float型,然后再才除以3. 结果:0.3333333333333333 (4) select dec(1,10,2)/3 from sysibm.sysdummy1; ---使用dec函数将1转换为decimal(10,2),然后除以3 结果:0.33333333333333333333333
其实如果想要把2个数的商四舍五入保存两位小数, oracle中可以直接使用round函数即可: select round(a/b,2) from dual; 而db2中却要绕几个弯才行:需要使用 select dec(cast(a as float)/b+0.005,10,2) from sysibm.sysdummy1; 先用cast转换a为float型,然后运算,再使用+0.005作为四舍五入,然后再使用dec截取2位小数。或者: select cast(round(cast(a as float)/b,2) as decimal(10,2)) from sysibm.sysdummy1; 先使用cast转a为float,然后运算,再使用round四舍五入取2位小数,然后使用cast转换为decimal(10,2)型。 例如: 1.Oracle数据库SQL语句转换成DB2数据库SQL语句 a.Oracle SQL语句 select * from (select t.device_name, count(t.device_name) as num, to_char(round(count(t.device_name) / (select sum(aa) from (select count(t.device_name) as aa from EQUIPMENT_MONITORING t where 1 = 1 and t.device_area = 'AA区' group by t.device_name)) * 100, 2), '990.99') || '%' as num_percent, sum(trunc(t.deal_time - t.occa_time) * 24 * 60 * 60) as occur from EQUIPMENT_MONITORING t where 1 = 1 and t.device_area = 'AA区' group by t.device_name) v where v.occur 0; b.DB2 SQL语句 select t.device_name, count(t.device_name) as num, CHAR(CAST(round(cast(count(t.device_name) as float) / (select sum(aa) from (select count(t.device_name) as aa from EQUIPMENT_MONITORING t where 1 = 1 and t.device_area = 'AA区' group by t.device_name)) * 100, 2) as decimal(3, 2))) || '%' as num_percent, sum(dec(t.deal_time - t.occa_time) * 24 * 60 * 60) as occur from EQUIPMENT_MONITORING t where 1 = 1 and t.device_area = 'AA区' group by t.device_name; |
CopyRight 2018-2019 实验室设备网 版权所有 |